﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.IO;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using KPIS.GERP.GBMS.BLL;
using KPIS.GERP.GBMS.IBLL;
using KPIS.GERP.GBMS.MODEL;
using KPIS.DBM;
using Telerik.Web.UI;
using KPIS.GERP.GBMS.APP_UI.Common;
using KPIS.GERP.GFMS.APP.App_UI.Commons;
using CrystalDecisions.Shared;

namespace KPIS.GERP.GBMS.APP_UI.Form
{
    public partial class BGM_Report : System.Web.UI.Page
    {
        Main main = new Main();
        MailMerge mailMerge = new MailMerge();
        private IProject projectBLL;
        private ComboBLL comboBLL = new ComboBLL();
        private SystemControlBLL scBLL = new SystemControlBLL();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                LoadYear();
                LoadJobSiteCombo();
                lbType.Text = Request.QueryString["type"].ToString();
                LoadAct();

                if (lbType.Text == "rq")
                    lbHeader.Text = "รายงานคำของบประมาณ";
                else if (lbType.Text == "ap")
                    lbHeader.Text = "รายงานพิจารณางบประมาณ";
                else if (lbType.Text == "rc")
                    lbHeader.Text = "รายงานจัดสรรงบประมาณ";
            }

            projectBLL = Project_Factory.GetProjectFactory(lbType.Text);
        }

        private void LoadYear()
        {
            int CurrYear = SystemInfo._CurrentYear;
            ddlYear.Items.Clear();

            for (int i = CurrYear + 1; i >= CurrYear - 10; i--)
            {
                ddlYear.Items.Add(new RadComboBoxItem(i.ToString(), i.ToString()));
            }

            ddlYear.SelectedValue = CurrYear.ToString();
        }

        private void LoadJobSiteCombo()
        {
            string jobSiteID = (string)new SsMng().GetUss(SsMng.SsUserType.WorkingJobSiteSeq);
            DataTable dt = comboBLL.ListJobsite();
            ddlJobSite.DataSource = dt;
            ddlJobSite.DataBind();

            ddlJobSite.Items.Insert(0, new RadComboBoxItem("----- หน่วยงานทั้งหมด -----", ""));
            ddlJobSite.SelectedValue = jobSiteID;
            if (jobSiteID != scBLL.JobSiteStrategyID())
            {
                ddlJobSite.Enabled = false;
            }
            else
            {
                ddlJobSite.Enabled = true;
            }
        }

        private void LoadAct()
        {
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            string strSQL = @"select s2.bgm_strat_list_seq, s2.bgm_strat_list_name
from bgm_strategy_data_list s1
inner join bgm_strategy_data_mapping map1 on s1.bgm_strat_list_seq = map1.bgm_strat_list_seq
inner join bgm_strategy_data_mapping map2 on map1.upper_bgm_strat_data_map_seq = map2.bgm_strat_data_map_seq
left join bgm_strategy_data_mapping map3 on map1.bgm_strat_data_map_seq = map3.upper_bgm_strat_data_map_seq
inner join bgm_strategy_data_list s2 on map2.bgm_strat_list_seq = s2.bgm_strat_list_seq
where map1.record_status <> 'D'
and map2.record_status <> 'D'
and map1.bgm_year = @bgm_year
and map3.bgm_strat_data_map_seq is null"
;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@bgm_year", ddlYear.SelectedValue);
                DataTable dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                ddlAct.DataSource = dt;
                ddlAct.DataBind();

                if (ddlAct.Items.Count > 0)
                {
                    ddlAct.SelectedIndex = 0;
                }
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        protected void rpt_01_Click(object sender, EventArgs e)
        {
            DataTable dtRaw = projectBLL.GetReport_01("2554");

            DataTable dtParam = new DataTable();

            DataTable[] dtSubReport = new DataTable[2];
            string[] subReportName = new string[2];

            dtSubReport[0] = projectBLL.GetReport_01_01("2554");
            dtSubReport[1] = projectBLL.GetReport_01_02("2554");
            //dtSubReport[2] = projectBLL.GetReport_01_01("2554");

            subReportName[0] = "Report_01_01";
            subReportName[1] = "Report_01_02";
            //subReportName[2] = "Report_01_01";

            dtParam.Columns.Add("ParamName");
            dtParam.Columns.Add("ParamValue");
            dtParam.Columns.Add("SubReportName");

            DataRow dr1 = dtParam.NewRow();

            dr1["ParamName"] = "Year";
            dr1["ParamValue"] = "2554";
            dr1["SubReportName"] = "";

            dtParam.Rows.Add(dr1);

            DataRow dr2 = dtParam.NewRow();

            dr2["ParamName"] = "ReportName";
            dr2["ParamValue"] = "Report_01" + " - " + lbHeader.Text;
            dr2["SubReportName"] = "";

            dtParam.Rows.Add(dr2);

            DataRow dr3 = dtParam.NewRow();

            dr3["ParamName"] = "KPI";
            dr3["ParamValue"] = (dtSubReport[0].Rows.Count > 0) ? "1" : "0";
            dr3["SubReportName"] = "";

            dtParam.Rows.Add(dr3);          

            Session["dtSubReport"] = dtSubReport;
            Session["strSubReportName"] = subReportName;

            Session["ReportName"] = "Report_01";
            Session["dtReport"] = dtRaw;
            Session["dtReportParam"] = dtParam;

            ScriptManager.RegisterClientScriptBlock(this.Page, this.GetType(), "Script", "openPopup();", true);
        }

        protected void rpt_202_Click(object sender, EventArgs e)
        {
            string strRecordStatus = rblRecordStatus.SelectedValue;
            string strType = rblType.SelectedValue;
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            DataTable ResultDTB = new DataTable();
            DataTable dtb;
            dbManager.Open();

            if (strRecordStatus == "W")
            {
                strRecordStatus = "";
            }

            try
            {
                string strSQL = @"
select r.bgm_req_seq, r.bgm_req_code, r.bgm_req_name
, r.date_start, r.date_end, j.job_site_name, r.bgm_req_remark
, coalesce(re1.amount, 0) as amount1, coalesce(re2.amount, 0) as amount2
, coalesce(re1.amount, 0) + coalesce(re2.amount, 0) as amount3
, st1.bgm_strat_list_seq as subact_seq
, map1.bgm_strat_list_code as subact_code
, st1.bgm_strat_list_name as subact_name
, st2.bgm_strat_list_seq as act_seq
, map2.bgm_strat_list_code as act_code
, st2.bgm_strat_list_name as act_name
from bgm_request r
left join bgm_strategy_data_mapping map1 on r.bgm_strat_list_seq = map1.bgm_strat_list_seq and map1.bgm_year = " + ddlYear.SelectedValue
+ @" left join bgm_strategy_data_mapping map2 on map1.upper_bgm_strat_data_map_seq = map2.bgm_strat_data_map_seq and map2.bgm_year = " + ddlYear.SelectedValue
+ @" left join bgm_strategy_data_list st1 on r.bgm_strat_list_seq = st1.bgm_strat_list_seq
left join bgm_strategy_data_list st2 on map2.bgm_strat_list_seq = st2.bgm_strat_list_seq
left join (select re.bgm_req_seq
	, sum(re.total_amount) as amount
	from bgm_request_expense re
	left join bgm_mas_expense exp on re.bgm_exp_seq = exp.bgm_exp_seq
	where re.bgm_source_seq = 1
	group by re.bgm_req_seq
	) re1 on r.bgm_req_seq = re1.bgm_req_seq
left join (select re.bgm_req_seq
	, sum(re.total_amount) as amount
	from bgm_request_expense re
	left join bgm_mas_expense exp on re.bgm_exp_seq = exp.bgm_exp_seq
	where re.bgm_source_seq = 11
	group by re.bgm_req_seq
) re2 on r.bgm_req_seq = re2.bgm_req_seq
left join hrm_mp_job_sites j on r.job_site_seq = j.job_site_seq

where 0 = 0"
                    + (strRecordStatus.Length > 0 ?
                        (strRecordStatus == "P" ? " and r.record_status in ('P', 'A')" : " and r.record_status = '" + strRecordStatus + "'")
                        : " and r.record_status not like 'D'")
                    + " and r.bgm_year = " + ddlYear.SelectedValue
                    + (strType == "2" ? " and r.prj_type_seq not in (5, 6)" : (strType == "3" ? " and r.prj_type_seq in (5, 6)" : ""))
                    + (string.IsNullOrEmpty(ddlJobSite.SelectedValue) ? "" : " and r.job_site_seq = " + ddlJobSite.SelectedValue);

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                var list = new List<Report202>();

                foreach(DataRow dr in dtb.Rows)
                {
                    var item = new Report202()
                    {
                        bgm_req_seq = dr["bgm_req_seq"].ToString(),
                        bgm_req_code = dr["bgm_req_code"].ToString(),
                        bgm_req_name = dr["bgm_req_name"].ToString(),
                        date_start = main.ConvertNullDateTime(dr["date_start"]).HasValue ? main.ConvertNullDateTime(dr["date_start"]).Value.ToShortDateString() : "",
                        date_end = main.ConvertNullDateTime(dr["date_end"]).HasValue ? main.ConvertNullDateTime(dr["date_end"]).Value.ToShortDateString() : "",
                        job_site_name = dr["job_site_name"].ToString(),
                        bgm_req_remark = dr["bgm_req_remark"].ToString(),
                        amount1 = main.ConvertNullDouble(dr["amount1"]) ?? 0,
                        amount2 = main.ConvertNullDouble(dr["amount2"]) ?? 0,
                        amount3 = main.ConvertNullDouble(dr["amount3"]) ?? 0,
                        subact_seq = dr["subact_seq"].ToString(),
                        subact_code = dr["subact_code"].ToString(),
                        subact_name = dr["subact_name"].ToString(),
                        act_seq = dr["act_seq"].ToString(),
                        act_code = dr["act_code"].ToString(),
                        act_name = dr["act_name"].ToString(),
                    };

                    list.Add(item);
                }

                var listSubact = from r in list
                                 group r by new
                                 {
                                     r.act_seq,
                                     r.subact_seq,
                                     r.subact_code,
                                     r.subact_name,
                                 } into g
                                 select new
                                 {
                                     act_seq = g.Key.act_seq,
                                     subact_seq = g.Key.subact_seq,
                                     subact_code = g.Key.subact_code,
                                     subact_name = g.Key.subact_name,
                                     amount1 = g.Sum(m => m.amount1),
                                     amount2 = g.Sum(m => m.amount2),
                                     amount3 = g.Sum(m => m.amount3),
                                 };

                var listAct = from r in list
                              group r by new
                              {
                                  r.act_seq,
                                  r.act_code,
                                  r.act_name,
                              } into g
                              select new
                              {
                                  act_seq = g.Key.act_seq,
                                  act_code = g.Key.act_code,
                                  act_name = g.Key.act_name,
                                  amount1 = g.Sum(m => m.amount1),
                                  amount2 = g.Sum(m => m.amount2),
                                  amount3 = g.Sum(m => m.amount3),
                              };

                var sum1 = list.Sum(m => m.amount1);
                var sum2 = list.Sum(m => m.amount2);
                var sum3 = list.Sum(m => m.amount3);

                Dictionary<string, object> dic = new Dictionary<string, object>();
                listAct = listAct.OrderBy(m => m.act_code);
                listSubact = listSubact.OrderBy(m => m.subact_code);
                int row = 5;

                dic.Add("A1", "สถาบันรับรองคุณภาพสถานพยาบาล (องค์การมหาชน) พ.ศ. " + ddlYear.SelectedValue);
                dic.Add("A3", "ปีงบประมาณ " + ddlYear.SelectedValue);
                dic.Add("E4", sum1);
                dic.Add("F4", sum2);
                dic.Add("G4", sum3);

                foreach (var itemAct in listAct)
                {
                    dic.Add("A" + row, itemAct.act_code + " : " + itemAct.act_name);
                    dic.Add("E" + row, itemAct.amount1.ToString("#,##0.00"));
                    dic.Add("F" + row, itemAct.amount2.ToString("#,##0.00"));
                    dic.Add("G" + row, itemAct.amount3.ToString("#,##0.00"));

                    row++;

                    foreach (var itemSubact in listSubact.Where(m => m.act_seq == itemAct.act_seq))
                    {
                        dic.Add("A" + row, "   " + itemSubact.subact_code + " : " + itemSubact.subact_name);
                        dic.Add("E" + row, itemSubact.amount1.ToString("#,##0.00"));
                        dic.Add("F" + row, itemSubact.amount2.ToString("#,##0.00"));
                        dic.Add("G" + row, itemSubact.amount3.ToString("#,##0.00"));

                        row++;

                        foreach (var item in list.Where(m => m.subact_seq == itemSubact.subact_seq))
                        {
                            dic.Add("A" + row, "      " + item.bgm_req_name);
                            dic.Add("B" + row, item.bgm_req_code);
                            dic.Add("C" + row, item.date_start);
                            dic.Add("D" + row, item.date_end);
                            dic.Add("E" + row, item.amount1.ToString("#,##0.00"));
                            dic.Add("F" + row, item.amount2.ToString("#,##0.00"));
                            dic.Add("G" + row, item.amount3.ToString("#,##0.00"));

                            row++;
                        }
                    }
                }

                string strTemplatePath = Server.MapPath("~/Reports/Templates/202.xlsx");
                mailMerge.MergeExcel(Response, strTemplatePath, string.Format("202-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), null,null,OfficeOpenXml.Table.TableStyles.None, dic,null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
            }
        }

        protected void rptReqDescr_Click(object sender, EventArgs e)
        {
            divReqDescr.Visible = true;
        }

        protected void cmdOk_Click(object sender, EventArgs e)
        {
            string strRecordStatus = rblRecordStatus.SelectedValue;
            string strType = rblType.SelectedValue;
            IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            DataTable ResultDTB = new DataTable();
            DataTable dtb;
            dbManager.Open();

            try
            {
                string strSQL = @"
select r.bgm_year
, l3.bgm_strat_list_name as output_name, l2.bgm_strat_list_name as act_name
, expg.bgm_exp_grp_name
, exp3.bgm_exp_name as bgm_exp_name3
, exp2.bgm_exp_name as bgm_exp_name2
, exp1.bgm_exp_name as bgm_exp_name1
, exp1.bgm_exp_seq as bgm_exp_seq1
, exp2.bgm_exp_seq as bgm_exp_seq2
, exp3.bgm_exp_seq as bgm_exp_seq3
, re.bgm_source_seq, s.bgm_source_name
, re2.total_amount
, re.descr
from bgm_request r
left join bgm_request_expense re on r.bgm_req_seq = re.bgm_req_seq
left join bgm_mas_expense exp1 on re.bgm_exp_seq = exp1.bgm_exp_seq
left join bgm_mas_expense exp2 on exp1.upper_bgm_exp_seq = exp2.bgm_exp_seq
left join bgm_mas_expense exp3 on exp2.upper_bgm_exp_seq = exp3.bgm_exp_seq
left join bgm_mas_expense_group expg on exp1.bgm_exp_grp_seq = expg.bgm_exp_grp_seq
left join bgm_mas_source s on re.bgm_source_seq = s.bgm_source_seq
left join bgm_strategy_data_mapping map1 on r.bgm_strat_list_seq = map1.bgm_strat_list_seq
left join bgm_strategy_data_mapping map2 on map1.upper_bgm_strat_data_map_seq = map2.bgm_strat_data_map_seq
left join bgm_strategy_data_mapping map3 on map2.upper_bgm_strat_data_map_seq = map3.bgm_strat_data_map_seq
left join bgm_strategy_data_list l1 on map1.bgm_strat_list_seq = l1.bgm_strat_list_seq
left join bgm_strategy_data_list l2 on map2.bgm_strat_list_seq = l2.bgm_strat_list_seq
left join bgm_strategy_data_list l3 on map3.bgm_strat_list_seq = l3.bgm_strat_list_seq

left join (select exp1.bgm_exp_seq, sum(re.total_amount) as total_amount
    from bgm_request r
    left join bgm_request_expense re on r.bgm_req_seq = re.bgm_req_seq
    left join bgm_mas_expense exp1 on re.bgm_exp_seq = exp1.bgm_exp_seq
    left join bgm_mas_expense exp2 on exp1.upper_bgm_exp_seq = exp2.bgm_exp_seq
    where r.record_status <> 'D'
    and r.bgm_year = " + ddlYear.SelectedValue
    + (strRecordStatus.Length > 0 ?
        (strRecordStatus == "P" ? " and r.record_status in ('P', 'A')" : " and r.record_status = '" + strRecordStatus + "'")
        : " and r.record_status not like 'D'")
    + (strType == "2" ? " and r.prj_type_seq not in (5, 6)" : (strType == "3" ? " and r.prj_type_seq in (5, 6)" : ""))
    + (string.IsNullOrEmpty(ddlJobSite.SelectedValue) ? "" : " and r.job_site_seq = " + ddlJobSite.SelectedValue)
    + @" group by exp1.bgm_exp_seq
) re2 on exp1.bgm_exp_seq = re2.bgm_exp_seq

where r.record_status <> 'D'"
                    + (strRecordStatus.Length > 0 ?
                        (strRecordStatus == "P" ? " and r.record_status in ('P', 'A')" : " and r.record_status = '" + strRecordStatus + "'")
                        : " and r.record_status not like 'D'")
                    + " and r.bgm_year = " + ddlYear.SelectedValue
                    + (strType == "2" ? " and r.prj_type_seq not in (5, 6)" : (strType == "3" ? " and r.prj_type_seq in (5, 6)" : ""))
                    + (string.IsNullOrEmpty(ddlJobSite.SelectedValue) ? "" : " and r.job_site_seq = " + ddlJobSite.SelectedValue)
                    + " and l2.bgm_strat_list_seq = " + ddlAct.SelectedValue
                    + " and expg.bgm_exp_grp_seq = " + rblBgmExpGrpSeq.SelectedValue
                    ;

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                dbManager.Dispose();
                throw ex;
            }
            
            CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
            rpt.Load(Server.MapPath("~/Reports/ReportReqDescr.rpt"));

            rpt.SetDataSource(dtb);

            rpt.ExportToHttpResponse(ExportFormatType.WordForWindows, Response, true, string.Format("request-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")));
        }

        protected void ddlYear_SelectedIndexChanged(object sender, RadComboBoxSelectedIndexChangedEventArgs e)
        {
            LoadAct();
        }
    }

    public class Report202
    {
        public string bgm_req_seq { get; set; }
        public string bgm_req_code { get; set; }
        public string bgm_req_name { get; set; }
        public string date_start { get; set; }
        public string date_end { get; set; }
        public string job_site_name { get; set; }
        public string bgm_req_remark { get; set; }
        public double amount1 { get; set; }
        public double amount2 { get; set; }
        public double amount3 { get; set; }
        public string subact_seq { get; set; }
        public string subact_code { get; set; }
        public string subact_name { get; set; }
        public string act_seq { get; set; }
        public string act_code { get; set; }
        public string act_name { get; set; }
    }
}
